-- @author shaodongyang
-- dwd 58主站详情日志标准化

use hdp_lbg_supin_zplisting;
set hive.auto.convert.join=true;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool.jar;
create temporary function LocalCatePathFormatUDF AS 'com.bj58.adsp.dp.hive.offline_udf.LocalCatePathFormatUDF';
create temporary function SpiderTagUDF AS 'com.bj58.adsp.dp.hive.offline_udf.SpiderTagUDF';

add jar viewfs://58-cluster/home/hdp_58dp/udf/com.bj58.lianmeng.dp.hive-udf-1.1.0-jar-with-dependencies.jar;
create temporary function src_dict_v110 as 'com.bj58.lianmeng.dp.hive.udf.SrcDictUDF';

with slot_base as (
    select
        slot slot_conf,
        (
            case
                when platform = 'pc' then 1
                when platform = 'm' then 2
                when platform = 'app' then 3
                else -999999
            end
        ) platform,
        ptype,
        biz,
        sense1,
        sense2,
        sense3
    from
        ods_zp_meta_slot
    where
        dt = '${dateSuffix}'
    group by
        slot,
        platform,
        ptype,
        biz,
        sense1,
        sense2,
        sense3
),
base_infodetail as (
SELECT
    tmp.*,
    StandardPlatformUDF(tmp.platform) platform1,
    StandardPlatformUDF(t2.platform) platform2,
    t2.ptype,
    t2.biz,
    BusinessLineExtractUDF(t2.biz, tmp.url, tmp.zpsrc, tmp.tjfrom_slot) business_line
FROM
    (
        SELECT
            t1.*,
            LocalCatePathFormatUDF(t1.displocalpath) displocalpath_arr,
            LocalCatePathFormatUDF(t1.dispcatepath) dispcatepath_arr,
            LocalCatePathFormatUDF(t1.clientlocalpath) clientlocalpath_arr,
            src_dict_v110(
                COALESCE(t1.utm_source, '-'),
                COALESCE(t1.spm, '-'),
                '${dateSuffix}'
            ) [1] source1,
            src_dict_v110(
                COALESCE(t1.utm_source, '-'),
                COALESCE(t1.spm, '-'),
                '${dateSuffix}'
            ) [2] source2,
            src_dict_v110(
                COALESCE(t1.utm_source, '-'),
                COALESCE(t1.spm, '-'),
                '${dateSuffix}'
            ) [3] source3,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.slot')) tjfrom_slot,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.sid')) tjfrom_sid,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.pid')) tjfrom_pid,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.infodisptype')) tjfrom_info_type,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.infotypes')) tjfrom_info_types,
            StandardStringUDF(get_json_object(tjfrom_format(t1.tjfrom_tmp), '$.pos')) tjfrom_pos,
            StandardCookieidUDF(t1.cookieid) cookieid2
        FROM
            (
                SELECT
                    *,
                    TjfromExtractUDF(url, tjfrom) tjfrom_tmp,
                    IF(info_types = '-', NULL, info_types) info_types_tmp
                FROM
                    ods_zp_n_n_infodetail
                WHERE
                    dt = '${dateSuffix}'
            ) t1
    ) tmp
    left join slot_base t2
    ON tmp.tjfrom_slot = t2.slot_conf
)
insert overwrite table dwd_zp_wb_n_infodetail partition(dt = '${dateSuffix}')
select
    StandardStringUDF(url) url,
    StandardStringUDF(refer_url) refer_url,
    StandardStringUDF(source1) source1,
    StandardStringUDF(source2) source2,
    StandardStringUDF(source3) source3,
    StandardStringUDF(IF(tjfrom_sid <> '-', tjfrom_sid, sid)) sid,
    StandardStringUDF(IF(tjfrom_pid <> '-', tjfrom_pid, pid)) pid,
    displocalpath_arr [0] disp_local1,
    displocalpath_arr [1] disp_local2,
    displocalpath_arr [2] disp_local3,
    clientlocalpath_arr [0] client_local1,
    clientlocalpath_arr [1] client_local2,
    clientlocalpath_arr [2] client_local3,
    dispcatepath_arr [0] disp_cate1,
    dispcatepath_arr [1] disp_cate2,
    dispcatepath_arr [2] disp_cate3,
    StandardIDUDF(userid) userid,
    UKeyExtractUDF(if(platform2=-999999, platform1, platform2), cookieid2, imei) ukey,
    cookieid2 cookieid,
    StandardStringUDF(imei) imei,
    StandardStringUDF(guestuuid) guestuuid,
    StandardStringUDF(ip) ip,
    StandardStringUDF(xforward) xforward,
    StandardStringUDF(useragent) useragent,
    SpiderTagUDF(useragent) spidertag,
    StandardOSUDF(os) os,
    StandardStringUDF(app_version) app_version,
    StandardTimestampUDF(stime) stime,
    if(platform2=-999999, platform1, platform2) platform,
    '-' div_id,
    StandardStringUDF(tjfrom_slot) slot,
    StandardLonLatUDF(longitude) longitude,
    StandardLonLatUDF(latitude) latitude,
    StandardStringUDF(source) source,
    StandardStringUDF(scene) scene,
    StandardRequestTypeUDF(tjfrom_info_type) info_type,
    StandardArraySplitUDF(
        IF(
            tjfrom_info_types <> '-',
            tjfrom_info_types,
            info_types_tmp
        ),
        ','
    ) info_types,
    StandardIntWithZeroUDF(tjfrom_pos) search_pos,
    StandardStringUDF(tjfrom_tmp) tjfrom,
    StandardIDUDF(info_id) info_id,
    StandardIDUDF(info_buid) info_buid,
    StandardIDUDF(info_parentuserid) info_parentuserid,
    StandardStringUDF(info_authentication) info_authentication,
    StandardStringUDF(info_work_address) info_work_address,
    StandardStringUDF(info_welfare) info_welfare,
    StandardIntUDF(info_click_cnt) info_click_cnt,
    StandardIntUDF(info_buser_vip) info_buser_vip,
    StandardIntUDF(info_buser_online) info_buser_online,
    StandardStringUDF(info_buser_job) info_buser_job,
    StandardIntUDF(info_buser_activity) info_buser_activity,
    StandardStringUDF(info_avg_salary) info_avg_salary,
    StandardStringUDF(zpsrc) zpsrc,
    StandardStringUDF(biz) biz,
    UrlFlagExtractUDF(url) url_flag,
    business_line,
    StandardStringUDF(openudid) openudid,
    StandardStringUDF(clientid) clientid,
    StandardIntWithZeroUDF(clientid_flag) clientid_flag
FROM
    base_infodetail
WHERE
    business_line = '58';
